{"cells": [{"cell_type": "markdown", "metadata": {}, "source": ["# Analyzing and predicting Service Request Types in DC"]}, {"cell_type": "markdown", "metadata": {"toc": true}, "source": ["<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n", "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Analyzing-and-predicting-Service-Request-Types-in-DC\" data-toc-modified-id=\"Analyzing-and-predicting-Service-Request-Types-in-DC-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Analyzing and predicting Service Request Types in DC</a></span><ul class=\"toc-item\"><li><span><a href=\"#Read-in-service-requests-for-2018\" data-toc-modified-id=\"Read-in-service-requests-for-2018-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Read in service requests for 2018</a></span></li><li><span><a href=\"#Read-in-Neighborhood-Clusters-dataset\" data-toc-modified-id=\"Read-in-Neighborhood-Clusters-dataset-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Read in Neighborhood Clusters dataset</a></span></li><li><span><a href=\"#Construct-model-that-predicts-service-type\" data-toc-modified-id=\"Construct-model-that-predicts-service-type-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Construct model that predicts service type</a></span><ul class=\"toc-item\"><li><span><a href=\"#Data-preprocessing\" data-toc-modified-id=\"Data-preprocessing-1.3.1\"><span class=\"toc-item-num\">1.3.1&nbsp;&nbsp;</span>Data preprocessing</a></span></li><li><span><a href=\"#Model-building\" data-toc-modified-id=\"Model-building-1.3.2\"><span class=\"toc-item-num\">1.3.2&nbsp;&nbsp;</span>Model building</a></span></li><li><span><a href=\"#Alternate-model,-excluding-the-department-codes\" data-toc-modified-id=\"Alternate-model,-excluding-the-department-codes-1.3.3\"><span class=\"toc-item-num\">1.3.3&nbsp;&nbsp;</span>Alternate model, excluding the department codes</a></span></li><li><span><a href=\"#How-many-requests-does-each-neighborhood-make?\" data-toc-modified-id=\"How-many-requests-does-each-neighborhood-make?-1.3.4\"><span class=\"toc-item-num\">1.3.4&nbsp;&nbsp;</span>How many requests does each neighborhood make?</a></span></li><li><span><a href=\"#What-kind-of-requests-does-each-neighborhood-mostly-make?\" data-toc-modified-id=\"What-kind-of-requests-does-each-neighborhood-mostly-make?-1.3.5\"><span class=\"toc-item-num\">1.3.5&nbsp;&nbsp;</span>What kind of requests does each neighborhood mostly make?</a></span></li></ul></li></ul></li></ul></div>"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The datasets used in this notebook are the \n", "1. __`City Service Requests in 2018`__\n", "2. __`Neighborhood Clusters`__\n", "\n", "These datasets can be found at [opendata.dc.gov](http://opendata.dc.gov/)\n", "\n", "We start by importing the ArcGIS package to load the data using a service URL"]}, {"cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": ["import arcgis\n", "from arcgis.features import *\n"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Read in service requests for 2018"]}, {"cell_type": "markdown", "metadata": {}, "source": ["[Link](http://opendata.dc.gov/datasets/city-service-requests-in-2018/geoservice?geometry=-77.49%2C38.811%2C-76.534%2C38.998) to Service Requests 2018 dataset"]}, {"cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [{"data": {"text/plain": ["<FeatureLayer url:\"https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/ServiceRequests/MapServer/9\">"]}, "execution_count": 2, "metadata": {}, "output_type": "execute_result"}], "source": ["requests_url = 'https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/ServiceRequests/MapServer/9'\n", "\n", "requests_layer = FeatureLayer(requests_url)\n", "requests_layer"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# Extract all the data and display number of rows\n", "requests_features = requests_layer.query()\n", "print('Total number of rows in the dataset: ')\n", "print(len(requests_features.features))"]}, {"cell_type": "markdown", "metadata": {}, "source": ["This dataset updates on runtime, hence the number of rows could vary each time."]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# store as dataframe\n", "requests = requests_features.sdf\n", "\n", "# View first 5 rows\n", "requests.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Read in Neighborhood Clusters dataset"]}, {"cell_type": "markdown", "metadata": {}, "source": ["[Link](http://opendata.dc.gov/datasets/neighborhood-clusters) to this dataset"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["neighborhood_url = 'https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/Administrative_Other_Boundaries_WebMercator/MapServer/17'\n", "\n", "neighborhood_layer = FeatureLayer(neighborhood_url)\n", "neighborhood_layer"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# Extract all the data and display number of rows\n", "neighborhood_features = neighborhood_layer.query()\n", "print('Total number of rows in the dataset: ')\n", "print(len(neighborhood_features.features))"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# store as dataframe\n", "neighborhood = neighborhood_features.sdf\n", "\n", "# View first 5 rows\n", "neighborhood.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["We now __merge__ the two datasets"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# Connect to the GIS\n", "from arcgis.gis import GIS\n", "gis = GIS('http://dcdev.maps.arcgis.com/', 'username', 'password')"]}, {"cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": ["# Perform spatial join between CBG layer and the service areas created for all time durations\n", "requests_with_neighborhood = arcgis.features.analysis.join_features(requests_url, neighborhood_url, spatial_relationship='Intersects', output_name='serviceRequests_Neighborhood_DC_1')"]}, {"cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [{"data": {"text/plain": ["{'itemId': '21ecb944fdc3495ea23e8cee411d0e29', 'notSharedWith': []}"]}, "execution_count": 10, "metadata": {}, "output_type": "execute_result"}], "source": ["requests_with_neighborhood.share(everyone=True)"]}, {"cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["Total number of rows in the dataset: \n", "80957\n"]}], "source": ["requests_with_neighborhood_url = str(requests_with_neighborhood.url)+'/0/'\n", "layer = FeatureLayer(requests_with_neighborhood_url)\n", "features = layer.query()\n", "print('Total number of rows in the dataset: ')\n", "print(len(features.features))"]}, {"cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>ADDDATE</th>\n", "      <th>CITY</th>\n", "      <th>DETAILS</th>\n", "      <th>INSPECTIONDATE</th>\n", "      <th>INSPECTIONFLAG</th>\n", "      <th>INSPECTORNAME</th>\n", "      <th>Join_Count</th>\n", "      <th>LATITUDE</th>\n", "      <th>LONGITUDE</th>\n", "      <th>MARADDRESSREPOSITORYID</th>\n", "      <th>...</th>\n", "      <th>STATE</th>\n", "      <th>STATUS_CODE</th>\n", "      <th>STREETADDRESS</th>\n", "      <th>TYPE</th>\n", "      <th>WARD</th>\n", "      <th>WEB_URL</th>\n", "      <th>XCOORD</th>\n", "      <th>YCOORD</th>\n", "      <th>ZIPCODE</th>\n", "      <th>SHAPE</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>1514882874000</td>\n", "      <td>WASHINGTON</td>\n", "      <td>jg - Not Found \u2013 Close SR</td>\n", "      <td>NaN</td>\n", "      <td>N</td>\n", "      <td>None</td>\n", "      <td>1</td>\n", "      <td>38.894965</td>\n", "      <td>-76.935862</td>\n", "      <td>19821.0</td>\n", "      <td>...</td>\n", "      <td>DC</td>\n", "      <td>CLOSED</td>\n", "      <td>4531 EADS STREET NE</td>\n", "      <td>Original</td>\n", "      <td>7</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>405564.01</td>\n", "      <td>136348.29</td>\n", "      <td>20019.0</td>\n", "      <td>{'x': 405564.0099999979, 'y': 136348.2899999991}</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1514883836000</td>\n", "      <td>WASHINGTON</td>\n", "      <td>am - Collected \u2013 Close SR</td>\n", "      <td>NaN</td>\n", "      <td>N</td>\n", "      <td>None</td>\n", "      <td>1</td>\n", "      <td>38.966837</td>\n", "      <td>-77.077571</td>\n", "      <td>285265.0</td>\n", "      <td>...</td>\n", "      <td>DC</td>\n", "      <td>CLOSED</td>\n", "      <td>5727 WESTERN AVENUE NW</td>\n", "      <td>Original</td>\n", "      <td>3</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>393277.48</td>\n", "      <td>144327.59</td>\n", "      <td>20015.0</td>\n", "      <td>{'x': 393277.4799999967, 'y': 144327.58999999985}</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>1514877377000</td>\n", "      <td>WASHINGTON</td>\n", "      <td>None</td>\n", "      <td>NaN</td>\n", "      <td>N</td>\n", "      <td>None</td>\n", "      <td>1</td>\n", "      <td>38.909417</td>\n", "      <td>-77.040607</td>\n", "      <td>238156.0</td>\n", "      <td>...</td>\n", "      <td>DC</td>\n", "      <td>CLOSED</td>\n", "      <td>1750 P STREET NW</td>\n", "      <td>Original</td>\n", "      <td>2</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>396478.07</td>\n", "      <td>137951.41</td>\n", "      <td>20036.0</td>\n", "      <td>{'x': 396478.0700000003, 'y': 137951.41000000015}</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>1514882441000</td>\n", "      <td>WASHINGTON</td>\n", "      <td>Per T. Duckett 1-9-18. closed by A .Hedgeman 0...</td>\n", "      <td>1.515497e+12</td>\n", "      <td>Y</td>\n", "      <td>None</td>\n", "      <td>1</td>\n", "      <td>38.927450</td>\n", "      <td>-77.097581</td>\n", "      <td>224717.0</td>\n", "      <td>...</td>\n", "      <td>DC</td>\n", "      <td>CLOSED</td>\n", "      <td>2895 UNIVERSITY TERRACE NW</td>\n", "      <td>Original</td>\n", "      <td>3</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>391538.72</td>\n", "      <td>139956.98</td>\n", "      <td>20016.0</td>\n", "      <td>{'x': 391538.7199999988, 'y': 139956.98000000045}</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>1514882008000</td>\n", "      <td>WASHINGTON</td>\n", "      <td>Collect on 1-6-18 by A.Hill</td>\n", "      <td>NaN</td>\n", "      <td>N</td>\n", "      <td>None</td>\n", "      <td>1</td>\n", "      <td>38.862290</td>\n", "      <td>-76.989317</td>\n", "      <td>67895.0</td>\n", "      <td>...</td>\n", "      <td>DC</td>\n", "      <td>CLOSED</td>\n", "      <td>1319 MAPLE VIEW PLACE SE</td>\n", "      <td>Original</td>\n", "      <td>8</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>400927.19</td>\n", "      <td>132719.19</td>\n", "      <td>20020.0</td>\n", "      <td>{'x': 400927.1899999976, 'y': 132719.19000000134}</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "<p>5 rows \u00d7 34 columns</p>\n", "</div>"], "text/plain": ["         ADDDATE        CITY  \\\n", "0  1514882874000  WASHINGTON   \n", "1  1514883836000  WASHINGTON   \n", "2  1514877377000  WASHINGTON   \n", "3  1514882441000  WASHINGTON   \n", "4  1514882008000  WASHINGTON   \n", "\n", "                                             DETAILS  INSPECTIONDATE  \\\n", "0                          jg - Not Found \u2013 Close SR             NaN   \n", "1                          am - Collected \u2013 Close SR             NaN   \n", "2                                               None             NaN   \n", "3  Per T. Duckett 1-9-18. closed by A .Hedgeman 0...    1.515497e+12   \n", "4                        Collect on 1-6-18 by A.Hill             NaN   \n", "\n", "  INSPECTIONFLAG INSPECTORNAME  Join_Count   LATITUDE  LONGITUDE  \\\n", "0              N          None           1  38.894965 -76.935862   \n", "1              N          None           1  38.966837 -77.077571   \n", "2              N          None           1  38.909417 -77.040607   \n", "3              Y          None           1  38.927450 -77.097581   \n", "4              N          None           1  38.862290 -76.989317   \n", "\n", "   MARADDRESSREPOSITORYID                        ...                          \\\n", "0                 19821.0                        ...                           \n", "1                285265.0                        ...                           \n", "2                238156.0                        ...                           \n", "3                224717.0                        ...                           \n", "4                 67895.0                        ...                           \n", "\n", "  STATE STATUS_CODE               STREETADDRESS      TYPE WARD  \\\n", "0    DC      CLOSED         4531 EADS STREET NE  Original    7   \n", "1    DC      CLOSED      5727 WESTERN AVENUE NW  Original    3   \n", "2    DC      CLOSED            1750 P STREET NW  Original    2   \n", "3    DC      CLOSED  2895 UNIVERSITY TERRACE NW  Original    3   \n", "4    DC      CLOSED    1319 MAPLE VIEW PLACE SE  Original    8   \n", "\n", "                   WEB_URL     XCOORD     YCOORD  ZIPCODE  \\\n", "0  http://planning.dc.gov/  405564.01  136348.29  20019.0   \n", "1  http://planning.dc.gov/  393277.48  144327.59  20015.0   \n", "2  http://planning.dc.gov/  396478.07  137951.41  20036.0   \n", "3  http://planning.dc.gov/  391538.72  139956.98  20016.0   \n", "4  http://planning.dc.gov/  400927.19  132719.19  20020.0   \n", "\n", "                                               SHAPE  \n", "0   {'x': 405564.0099999979, 'y': 136348.2899999991}  \n", "1  {'x': 393277.4799999967, 'y': 144327.58999999985}  \n", "2  {'x': 396478.0700000003, 'y': 137951.41000000015}  \n", "3  {'x': 391538.7199999988, 'y': 139956.98000000045}  \n", "4  {'x': 400927.1899999976, 'y': 132719.19000000134}  \n", "\n", "[5 rows x 34 columns]"]}, "execution_count": 12, "metadata": {}, "output_type": "execute_result"}], "source": ["merged = features.sdf\n", "merged.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Construct model that predicts service type\n", "\n", "The variables used to build the model are:\n", "> 1. City Quadrant\n", "> 2. Neighborhood cluster\n", "> 3. Ward (Geographical unit)\n", "> 4. Organization acronym\n", "> 5. Status Code"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Data preprocessing"]}, {"cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": ["quads = ['NE', 'NW', 'SE', 'SW']\n", "def generate_quadrant(x):\n", "    '''Function that extracts quadrant from street address'''\n", "    try:\n", "        temp = x[-2:]\n", "        if temp in quads:\n", "            return temp\n", "        else:\n", "            return 'NaN'\n", "    except:\n", "        return 'NaN'"]}, {"cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [{"data": {"text/plain": ["0    NE\n", "1    NW\n", "2    NW\n", "3    NW\n", "4    SE\n", "Name: QUADRANT, dtype: object"]}, "execution_count": 14, "metadata": {}, "output_type": "execute_result"}], "source": ["merged['QUADRANT'] = merged['STREETADDRESS'].apply(generate_quadrant)\n", "merged['QUADRANT'].head()"]}, {"cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [{"data": {"text/plain": ["array(['NE', 'NW', 'SE', 'NaN', 'SW'], dtype=object)"]}, "execution_count": 15, "metadata": {}, "output_type": "execute_result"}], "source": ["merged['QUADRANT'].unique()"]}, {"cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [{"data": {"text/plain": ["0    30\n", "1    10\n", "2     6\n", "3    13\n", "4    28\n", "Name: CLUSTER, dtype: object"]}, "execution_count": 16, "metadata": {}, "output_type": "execute_result"}], "source": ["merged['CLUSTER'] = merged['NAME'].apply(lambda x: x[8:])\n", "merged['CLUSTER'].head()"]}, {"cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": ["merged['CLUSTER'] = merged['CLUSTER'].astype(int)"]}, {"cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [{"data": {"text/plain": ["array(['DPW', 'DDOT', 'FEMS', 'DOH', 'OUC', 'DOEE', 'DMV', 'ORM',\n", "       'DC-ICH', 'DDS'], dtype=object)"]}, "execution_count": 18, "metadata": {}, "output_type": "execute_result"}], "source": ["merged['ORGANIZATIONACRONYM'].unique()"]}, {"cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [{"data": {"text/plain": ["array(['CLOSED', 'OPEN'], dtype=object)"]}, "execution_count": 19, "metadata": {}, "output_type": "execute_result"}], "source": ["merged['STATUS_CODE'].unique()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Let's extract the number of possible outcomes, i.e. length of the target variable and also take a look at the values"]}, {"cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [{"data": {"text/plain": ["23"]}, "execution_count": 20, "metadata": {}, "output_type": "execute_result"}], "source": ["len(merged['SERVICETYPECODEDESCRIPTION'].unique())"]}, {"cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [{"data": {"text/plain": ["array(['SNOW', 'PEMA- Parking Enforcement Management Administration',\n", "       'Toa-Street & Bridge Maintenance', 'Urban Forrestry',\n", "       'SWMA- Solid Waste Management Admistration',\n", "       'Transportation Operations Administration', 'SIOD',\n", "       'Department of Transportation', 'Driver Vehicle Services',\n", "       'Toa-Trans Sys Mnt-Signs', 'Toa- Trans Sys Mnt',\n", "       'DOH- Department Of Health', 'Tru-311',\n", "       'Transportation Policy & Planning Administration',\n", "       'FEMS-Smoke Alarms', 'FEMS-Special Events',\n", "       'Department of Energy and Environment', 'Adjudication Services',\n", "       '311- Call Center', 'HOMYDRPR- How Is My Driving Program',\n", "       'DC Interagency Council on Homelessness', '311- Emergencies',\n", "       'Department of Disability Services'], dtype=object)"]}, "execution_count": 21, "metadata": {}, "output_type": "execute_result"}], "source": ["requests['SERVICETYPECODEDESCRIPTION'].unique()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Model building"]}, {"cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": ["# Import necessary packages\n", "from sklearn.preprocessing import *\n", "from sklearn.model_selection import train_test_split\n", "from sklearn.ensemble import RandomForestClassifier\n", "from sklearn.metrics import accuracy_score"]}, {"cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": ["# Convert categorical (text) fields to numbers\n", "number = LabelEncoder()\n", "merged['SERVICETYPE_NUMBER'] = number.fit_transform(merged['SERVICETYPECODEDESCRIPTION'].astype('str'))\n", "merged['STATUS_CODE_NUMBER'] = number.fit_transform(merged['STATUS_CODE'].astype('str'))"]}, {"cell_type": "code", "execution_count": 24, "metadata": {"scrolled": true}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>SERVICETYPECODEDESCRIPTION</th>\n", "      <th>SERVICETYPE_NUMBER</th>\n", "      <th>QUADRANT</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>ORGANIZATIONACRONYM</th>\n", "      <th>STATUS_CODE</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>NE</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>NW</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>PEMA- Parking Enforcement Management Administr...</td>\n", "      <td>12</td>\n", "      <td>NW</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>SNOW</td>\n", "      <td>14</td>\n", "      <td>NW</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>SE</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   index                         SERVICETYPECODEDESCRIPTION  \\\n", "0      0          SWMA- Solid Waste Management Admistration   \n", "1      1          SWMA- Solid Waste Management Admistration   \n", "2      2  PEMA- Parking Enforcement Management Administr...   \n", "3      3                                               SNOW   \n", "4      4          SWMA- Solid Waste Management Admistration   \n", "\n", "   SERVICETYPE_NUMBER QUADRANT  CLUSTER WARD ORGANIZATIONACRONYM STATUS_CODE  \\\n", "0                  15       NE       30    7                 DPW      CLOSED   \n", "1                  15       NW       10    3                 DPW      CLOSED   \n", "2                  12       NW        6    2                 DPW      CLOSED   \n", "3                  14       NW       13    3                 DPW      CLOSED   \n", "4                  15       SE       28    8                 DPW      CLOSED   \n", "\n", "   STATUS_CODE_NUMBER  \n", "0                   0  \n", "1                   0  \n", "2                   0  \n", "3                   0  \n", "4                   0  "]}, "execution_count": 24, "metadata": {}, "output_type": "execute_result"}], "source": ["# Extract desired fields\n", "data = merged[['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'QUADRANT', 'CLUSTER', 'WARD', 'ORGANIZATIONACRONYM', 'STATUS_CODE', 'STATUS_CODE_NUMBER']]\n", "data.reset_index(inplace=True)\n", "data.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Let's binarize values in fields `QUADRANT` (4) and `ORGANIZATIONACRONYM` (8)\n", "\n", "Wonder why are not doing it for `CLUSTER`? Appropriate nomenclature of [adjacent clusters](http://opendata.dc.gov/datasets/neighborhood-clusters)."]}, {"cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>SERVICETYPECODEDESCRIPTION</th>\n", "      <th>SERVICETYPE_NUMBER</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>STATUS_CODE</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "      <th>QUADRANT_NE</th>\n", "      <th>QUADRANT_NW</th>\n", "      <th>QUADRANT_NaN</th>\n", "      <th>...</th>\n", "      <th>ORGANIZATIONACRONYM_DC-ICH</th>\n", "      <th>ORGANIZATIONACRONYM_DDOT</th>\n", "      <th>ORGANIZATIONACRONYM_DDS</th>\n", "      <th>ORGANIZATIONACRONYM_DMV</th>\n", "      <th>ORGANIZATIONACRONYM_DOEE</th>\n", "      <th>ORGANIZATIONACRONYM_DOH</th>\n", "      <th>ORGANIZATIONACRONYM_DPW</th>\n", "      <th>ORGANIZATIONACRONYM_FEMS</th>\n", "      <th>ORGANIZATIONACRONYM_ORM</th>\n", "      <th>ORGANIZATIONACRONYM_OUC</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>...</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>...</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>PEMA- Parking Enforcement Management Administr...</td>\n", "      <td>12</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>...</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>SNOW</td>\n", "      <td>14</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>...</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>...</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "<p>5 rows \u00d7 22 columns</p>\n", "</div>"], "text/plain": ["   index                         SERVICETYPECODEDESCRIPTION  \\\n", "0      0          SWMA- Solid Waste Management Admistration   \n", "1      1          SWMA- Solid Waste Management Admistration   \n", "2      2  PEMA- Parking Enforcement Management Administr...   \n", "3      3                                               SNOW   \n", "4      4          SWMA- Solid Waste Management Admistration   \n", "\n", "   SERVICETYPE_NUMBER  CLUSTER WARD STATUS_CODE  STATUS_CODE_NUMBER  \\\n", "0                  15       30    7      CLOSED                   0   \n", "1                  15       10    3      CLOSED                   0   \n", "2                  12        6    2      CLOSED                   0   \n", "3                  14       13    3      CLOSED                   0   \n", "4                  15       28    8      CLOSED                   0   \n", "\n", "   QUADRANT_NE  QUADRANT_NW  QUADRANT_NaN           ...             \\\n", "0            1            0             0           ...              \n", "1            0            1             0           ...              \n", "2            0            1             0           ...              \n", "3            0            1             0           ...              \n", "4            0            0             0           ...              \n", "\n", "   ORGANIZATIONACRONYM_DC-ICH  ORGANIZATIONACRONYM_DDOT  \\\n", "0                           0                         0   \n", "1                           0                         0   \n", "2                           0                         0   \n", "3                           0                         0   \n", "4                           0                         0   \n", "\n", "   ORGANIZATIONACRONYM_DDS  ORGANIZATIONACRONYM_DMV  ORGANIZATIONACRONYM_DOEE  \\\n", "0                        0                        0                         0   \n", "1                        0                        0                         0   \n", "2                        0                        0                         0   \n", "3                        0                        0                         0   \n", "4                        0                        0                         0   \n", "\n", "   ORGANIZATIONACRONYM_DOH  ORGANIZATIONACRONYM_DPW  ORGANIZATIONACRONYM_FEMS  \\\n", "0                        0                        1                         0   \n", "1                        0                        1                         0   \n", "2                        0                        1                         0   \n", "3                        0                        1                         0   \n", "4                        0                        1                         0   \n", "\n", "   ORGANIZATIONACRONYM_ORM  ORGANIZATIONACRONYM_OUC  \n", "0                        0                        0  \n", "1                        0                        0  \n", "2                        0                        0  \n", "3                        0                        0  \n", "4                        0                        0  \n", "\n", "[5 rows x 22 columns]"]}, "execution_count": 25, "metadata": {}, "output_type": "execute_result"}], "source": ["import pandas as pd\n", "data = pd.get_dummies(data=data, columns=['QUADRANT', 'ORGANIZATIONACRONYM'])\n", "data.head()"]}, {"cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "      <th>QUADRANT_NE</th>\n", "      <th>QUADRANT_NW</th>\n", "      <th>QUADRANT_NaN</th>\n", "      <th>QUADRANT_SE</th>\n", "      <th>QUADRANT_SW</th>\n", "      <th>ORGANIZATIONACRONYM_DC-ICH</th>\n", "      <th>ORGANIZATIONACRONYM_DDOT</th>\n", "      <th>ORGANIZATIONACRONYM_DDS</th>\n", "      <th>ORGANIZATIONACRONYM_DMV</th>\n", "      <th>ORGANIZATIONACRONYM_DOEE</th>\n", "      <th>ORGANIZATIONACRONYM_DOH</th>\n", "      <th>ORGANIZATIONACRONYM_DPW</th>\n", "      <th>ORGANIZATIONACRONYM_FEMS</th>\n", "      <th>ORGANIZATIONACRONYM_ORM</th>\n", "      <th>ORGANIZATIONACRONYM_OUC</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   index  CLUSTER WARD  STATUS_CODE_NUMBER  QUADRANT_NE  QUADRANT_NW  \\\n", "0      0       30    7                   0            1            0   \n", "1      1       10    3                   0            0            1   \n", "2      2        6    2                   0            0            1   \n", "3      3       13    3                   0            0            1   \n", "4      4       28    8                   0            0            0   \n", "\n", "   QUADRANT_NaN  QUADRANT_SE  QUADRANT_SW  ORGANIZATIONACRONYM_DC-ICH  \\\n", "0             0            0            0                           0   \n", "1             0            0            0                           0   \n", "2             0            0            0                           0   \n", "3             0            0            0                           0   \n", "4             0            1            0                           0   \n", "\n", "   ORGANIZATIONACRONYM_DDOT  ORGANIZATIONACRONYM_DDS  ORGANIZATIONACRONYM_DMV  \\\n", "0                         0                        0                        0   \n", "1                         0                        0                        0   \n", "2                         0                        0                        0   \n", "3                         0                        0                        0   \n", "4                         0                        0                        0   \n", "\n", "   ORGANIZATIONACRONYM_DOEE  ORGANIZATIONACRONYM_DOH  ORGANIZATIONACRONYM_DPW  \\\n", "0                         0                        0                        1   \n", "1                         0                        0                        1   \n", "2                         0                        0                        1   \n", "3                         0                        0                        1   \n", "4                         0                        0                        1   \n", "\n", "   ORGANIZATIONACRONYM_FEMS  ORGANIZATIONACRONYM_ORM  ORGANIZATIONACRONYM_OUC  \n", "0                         0                        0                        0  \n", "1                         0                        0                        0  \n", "2                         0                        0                        0  \n", "3                         0                        0                        0  \n", "4                         0                        0                        0  "]}, "execution_count": 26, "metadata": {}, "output_type": "execute_result"}], "source": ["# Extract input dataframe\n", "model_data = data.drop(['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'STATUS_CODE'], axis=1)\n", "model_data.head()"]}, {"cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": ["def handle_ward(x):\n", "    accept = [range(0,8)]\n", "    if x not in accept:\n", "        return 0\n", "    else:\n", "        return x"]}, {"cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": ["model_data['WARD'] = model_data['WARD'].apply(handle_ward)"]}, {"cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [], "source": ["# Define independent and dependent variables\n", "y = data['SERVICETYPE_NUMBER'].values\n", "X = model_data.values"]}, {"cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": ["# Split data into training and test samples of 70%-30%\n", "X_train, X_test, y_train, y_test = train_test_split(X,y, test_size = .3, random_state=522, stratify=y)"]}, {"cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["[15 15 12 ... 15 21 15]\n"]}], "source": ["# n_estimators = number of trees in the forest\n", "# min_samples_leaf = minimum number of samples required to be at a leaf node for the tree\n", "rf = RandomForestClassifier(n_estimators=2500, min_samples_leaf=5, random_state=522)\n", "rf.fit(X_train, y_train)\n", "y_pred = rf.predict(X_test)\n", "print(y_pred)"]}, {"cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["Accuracy:  0.6824769433465085\n"]}], "source": ["print('Accuracy: ', accuracy_score(y_test, y_pred))"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Alternate model, excluding the department codes"]}, {"cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>SERVICETYPECODEDESCRIPTION</th>\n", "      <th>SERVICETYPE_NUMBER</th>\n", "      <th>QUADRANT</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>ORGANIZATIONACRONYM</th>\n", "      <th>STATUS_CODE</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>NE</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>NW</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>PEMA- Parking Enforcement Management Administr...</td>\n", "      <td>12</td>\n", "      <td>NW</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>SNOW</td>\n", "      <td>14</td>\n", "      <td>NW</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>SE</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   index                         SERVICETYPECODEDESCRIPTION  \\\n", "0      0          SWMA- Solid Waste Management Admistration   \n", "1      1          SWMA- Solid Waste Management Admistration   \n", "2      2  PEMA- Parking Enforcement Management Administr...   \n", "3      3                                               SNOW   \n", "4      4          SWMA- Solid Waste Management Admistration   \n", "\n", "   SERVICETYPE_NUMBER QUADRANT  CLUSTER WARD ORGANIZATIONACRONYM STATUS_CODE  \\\n", "0                  15       NE       30    7                 DPW      CLOSED   \n", "1                  15       NW       10    3                 DPW      CLOSED   \n", "2                  12       NW        6    2                 DPW      CLOSED   \n", "3                  14       NW       13    3                 DPW      CLOSED   \n", "4                  15       SE       28    8                 DPW      CLOSED   \n", "\n", "   STATUS_CODE_NUMBER  \n", "0                   0  \n", "1                   0  \n", "2                   0  \n", "3                   0  \n", "4                   0  "]}, "execution_count": 33, "metadata": {}, "output_type": "execute_result"}], "source": ["data = merged[['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'QUADRANT', 'CLUSTER', 'WARD', 'ORGANIZATIONACRONYM', 'STATUS_CODE', 'STATUS_CODE_NUMBER']]\n", "data.reset_index(inplace=True)\n", "data.head()"]}, {"cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>SERVICETYPECODEDESCRIPTION</th>\n", "      <th>SERVICETYPE_NUMBER</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>ORGANIZATIONACRONYM</th>\n", "      <th>STATUS_CODE</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "      <th>QUADRANT_NE</th>\n", "      <th>QUADRANT_NW</th>\n", "      <th>QUADRANT_NaN</th>\n", "      <th>QUADRANT_SE</th>\n", "      <th>QUADRANT_SW</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>PEMA- Parking Enforcement Management Administr...</td>\n", "      <td>12</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>SNOW</td>\n", "      <td>14</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>SWMA- Solid Waste Management Admistration</td>\n", "      <td>15</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>DPW</td>\n", "      <td>CLOSED</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   index                         SERVICETYPECODEDESCRIPTION  \\\n", "0      0          SWMA- Solid Waste Management Admistration   \n", "1      1          SWMA- Solid Waste Management Admistration   \n", "2      2  PEMA- Parking Enforcement Management Administr...   \n", "3      3                                               SNOW   \n", "4      4          SWMA- Solid Waste Management Admistration   \n", "\n", "   SERVICETYPE_NUMBER  CLUSTER WARD ORGANIZATIONACRONYM STATUS_CODE  \\\n", "0                  15       30    7                 DPW      CLOSED   \n", "1                  15       10    3                 DPW      CLOSED   \n", "2                  12        6    2                 DPW      CLOSED   \n", "3                  14       13    3                 DPW      CLOSED   \n", "4                  15       28    8                 DPW      CLOSED   \n", "\n", "   STATUS_CODE_NUMBER  QUADRANT_NE  QUADRANT_NW  QUADRANT_NaN  QUADRANT_SE  \\\n", "0                   0            1            0             0            0   \n", "1                   0            0            1             0            0   \n", "2                   0            0            1             0            0   \n", "3                   0            0            1             0            0   \n", "4                   0            0            0             0            1   \n", "\n", "   QUADRANT_SW  \n", "0            0  \n", "1            0  \n", "2            0  \n", "3            0  \n", "4            0  "]}, "execution_count": 34, "metadata": {}, "output_type": "execute_result"}], "source": ["data1 = pd.get_dummies(data=data,columns=['QUADRANT'])\n", "data1.head()"]}, {"cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>CLUSTER</th>\n", "      <th>WARD</th>\n", "      <th>STATUS_CODE_NUMBER</th>\n", "      <th>QUADRANT_NE</th>\n", "      <th>QUADRANT_NW</th>\n", "      <th>QUADRANT_NaN</th>\n", "      <th>QUADRANT_SE</th>\n", "      <th>QUADRANT_SW</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>30</td>\n", "      <td>7</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>10</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2</td>\n", "      <td>6</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>3</td>\n", "      <td>13</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>4</td>\n", "      <td>28</td>\n", "      <td>8</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   index  CLUSTER WARD  STATUS_CODE_NUMBER  QUADRANT_NE  QUADRANT_NW  \\\n", "0      0       30    7                   0            1            0   \n", "1      1       10    3                   0            0            1   \n", "2      2        6    2                   0            0            1   \n", "3      3       13    3                   0            0            1   \n", "4      4       28    8                   0            0            0   \n", "\n", "   QUADRANT_NaN  QUADRANT_SE  QUADRANT_SW  \n", "0             0            0            0  \n", "1             0            0            0  \n", "2             0            0            0  \n", "3             0            0            0  \n", "4             0            1            0  "]}, "execution_count": 35, "metadata": {}, "output_type": "execute_result"}], "source": ["model_data1 = data1.drop(['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'STATUS_CODE', 'ORGANIZATIONACRONYM'], axis=1)\n", "model_data1.head()"]}, {"cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": ["model_data1['WARD'] = model_data1['WARD'].apply(handle_ward)"]}, {"cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": ["y = data['SERVICETYPE_NUMBER'].values\n", "X = model_data1.values"]}, {"cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [], "source": ["# Split data into training and test samples of 70%-30%\n", "X_train, X_test, y_train, y_test = train_test_split(X,y, test_size = .3, random_state=522, stratify=y)"]}, {"cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["[15 15 12 ... 15 15 15]\n"]}], "source": ["# n_estimators = number of trees in the forest\n", "# min_samples_leaf = minimum number of samples required to be at a leaf node for the tree\n", "rf = RandomForestClassifier(n_estimators=2500, min_samples_leaf=5, random_state=522)\n", "rf.fit(X_train, y_train)\n", "y_pred = rf.predict(X_test)\n", "print(y_pred)"]}, {"cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["Accuracy:  0.4862895256916996\n"]}], "source": ["print('Accuracy: ', accuracy_score(y_test, y_pred))"]}, {"cell_type": "markdown", "metadata": {}, "source": ["A drop in accuracy from __68.39%__ to __48.78%__ demonstrates the importance of using the correct predictors."]}, {"cell_type": "markdown", "metadata": {}, "source": ["### How many requests does each neighborhood make?"]}, {"cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>NAME</th>\n", "      <th>counts</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>Cluster 1</td>\n", "      <td>1991</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Cluster 10</td>\n", "      <td>1707</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Cluster 11</td>\n", "      <td>2475</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Cluster 12</td>\n", "      <td>767</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Cluster 13</td>\n", "      <td>1625</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["         NAME  counts\n", "0   Cluster 1    1991\n", "1  Cluster 10    1707\n", "2  Cluster 11    2475\n", "3  Cluster 12     767\n", "4  Cluster 13    1625"]}, "execution_count": 41, "metadata": {}, "output_type": "execute_result"}], "source": ["# Count of service requests per cluster\n", "cluster_count = merged.groupby('NAME').size().reset_index(name='counts')\n", "cluster_count.head()"]}, {"cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>NAME</th>\n", "      <th>NBH_NAMES</th>\n", "      <th>OBJECTID</th>\n", "      <th>Shape_Area</th>\n", "      <th>Shape_Length</th>\n", "      <th>TYPE</th>\n", "      <th>WEB_URL</th>\n", "      <th>SHAPE</th>\n", "      <th>counts</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>Cluster 39</td>\n", "      <td>Congress Heights, Bellevue, Washington Highlands</td>\n", "      <td>1</td>\n", "      <td>4.886463e+06</td>\n", "      <td>10711.668010</td>\n", "      <td>Original</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>{'rings': [[[-8570934.978117127, 4699521.51243...</td>\n", "      <td>2360</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Cluster 38</td>\n", "      <td>Douglas, Shipley Terrace</td>\n", "      <td>2</td>\n", "      <td>2.367958e+06</td>\n", "      <td>8229.486324</td>\n", "      <td>Original</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>{'rings': [[[-8568786.426828014, 4700618.41227...</td>\n", "      <td>733</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Cluster 36</td>\n", "      <td>Woodland/Fort Stanton, Garfield Heights, Knox ...</td>\n", "      <td>3</td>\n", "      <td>1.119573e+06</td>\n", "      <td>4746.344457</td>\n", "      <td>Original</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>{'rings': [[[-8568124.617559846, 4701733.64556...</td>\n", "      <td>378</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Cluster 27</td>\n", "      <td>Near Southeast, Navy Yard</td>\n", "      <td>4</td>\n", "      <td>1.619167e+06</td>\n", "      <td>7286.968902</td>\n", "      <td>Original</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>{'rings': [[[-8570182.535376322, 4704085.08115...</td>\n", "      <td>480</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Cluster 32</td>\n", "      <td>River Terrace, Benning, Greenway, Dupont Park</td>\n", "      <td>5</td>\n", "      <td>4.286254e+06</td>\n", "      <td>11251.012821</td>\n", "      <td>Original</td>\n", "      <td>http://planning.dc.gov/</td>\n", "      <td>{'rings': [[[-8564654.618529493, 4705921.46259...</td>\n", "      <td>1285</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["         NAME                                          NBH_NAMES  OBJECTID  \\\n", "0  Cluster 39   Congress Heights, Bellevue, Washington Highlands         1   \n", "1  Cluster 38                           Douglas, Shipley Terrace         2   \n", "2  Cluster 36  Woodland/Fort Stanton, Garfield Heights, Knox ...         3   \n", "3  Cluster 27                          Near Southeast, Navy Yard         4   \n", "4  Cluster 32      River Terrace, Benning, Greenway, Dupont Park         5   \n", "\n", "     Shape_Area  Shape_Length      TYPE                  WEB_URL  \\\n", "0  4.886463e+06  10711.668010  Original  http://planning.dc.gov/   \n", "1  2.367958e+06   8229.486324  Original  http://planning.dc.gov/   \n", "2  1.119573e+06   4746.344457  Original  http://planning.dc.gov/   \n", "3  1.619167e+06   7286.968902  Original  http://planning.dc.gov/   \n", "4  4.286254e+06  11251.012821  Original  http://planning.dc.gov/   \n", "\n", "                                               SHAPE  counts  \n", "0  {'rings': [[[-8570934.978117127, 4699521.51243...    2360  \n", "1  {'rings': [[[-8568786.426828014, 4700618.41227...     733  \n", "2  {'rings': [[[-8568124.617559846, 4701733.64556...     378  \n", "3  {'rings': [[[-8570182.535376322, 4704085.08115...     480  \n", "4  {'rings': [[[-8564654.618529493, 4705921.46259...    1285  "]}, "execution_count": 42, "metadata": {}, "output_type": "execute_result"}], "source": ["# merge with original file\n", "neighborhood = pd.merge(neighborhood, cluster_count, on='NAME')\n", "neighborhood.head()"]}, {"cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>NAME</th>\n", "      <th>NBH_NAMES</th>\n", "      <th>counts</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>33</th>\n", "      <td>Cluster 2</td>\n", "      <td>Columbia Heights, Mt. Pleasant, Pleasant Plain...</td>\n", "      <td>5291</td>\n", "    </tr>\n", "    <tr>\n", "      <th>30</th>\n", "      <td>Cluster 25</td>\n", "      <td>Union Station, Stanton Park, Kingman Park</td>\n", "      <td>5277</td>\n", "    </tr>\n", "    <tr>\n", "      <th>20</th>\n", "      <td>Cluster 18</td>\n", "      <td>Brightwood Park, Crestwood, Petworth</td>\n", "      <td>5137</td>\n", "    </tr>\n", "    <tr>\n", "      <th>13</th>\n", "      <td>Cluster 6</td>\n", "      <td>Dupont Circle, Connecticut Avenue/K Street</td>\n", "      <td>4629</td>\n", "    </tr>\n", "    <tr>\n", "      <th>38</th>\n", "      <td>Cluster 26</td>\n", "      <td>Capitol Hill, Lincoln Park</td>\n", "      <td>3876</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Cluster 8</td>\n", "      <td>Downtown, Chinatown, Penn Quarters, Mount Vern...</td>\n", "      <td>3763</td>\n", "    </tr>\n", "    <tr>\n", "      <th>32</th>\n", "      <td>Cluster 21</td>\n", "      <td>Edgewood, Bloomingdale, Truxton Circle, Eckington</td>\n", "      <td>3554</td>\n", "    </tr>\n", "    <tr>\n", "      <th>21</th>\n", "      <td>Cluster 11</td>\n", "      <td>Friendship Heights, American University Park, ...</td>\n", "      <td>2475</td>\n", "    </tr>\n", "    <tr>\n", "      <th>23</th>\n", "      <td>Cluster 17</td>\n", "      <td>Takoma, Brightwood, Manor Park</td>\n", "      <td>2467</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Cluster 5</td>\n", "      <td>West End, Foggy Bottom, GWU</td>\n", "      <td>2445</td>\n", "    </tr>\n", "    <tr>\n", "      <th>29</th>\n", "      <td>Cluster 34</td>\n", "      <td>Twining, Fairlawn, Randle Highlands, Penn Bran...</td>\n", "      <td>2429</td>\n", "    </tr>\n", "    <tr>\n", "      <th>14</th>\n", "      <td>Cluster 3</td>\n", "      <td>Howard University, Le Droit Park, Cardozo/Shaw</td>\n", "      <td>2412</td>\n", "    </tr>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>Cluster 39</td>\n", "      <td>Congress Heights, Bellevue, Washington Highlands</td>\n", "      <td>2360</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>Cluster 7</td>\n", "      <td>Shaw, Logan Circle</td>\n", "      <td>2270</td>\n", "    </tr>\n", "    <tr>\n", "      <th>12</th>\n", "      <td>Cluster 4</td>\n", "      <td>Georgetown, Burleith/Hillandale</td>\n", "      <td>2243</td>\n", "    </tr>\n", "    <tr>\n", "      <th>16</th>\n", "      <td>Cluster 33</td>\n", "      <td>Capitol View, Marshall Heights, Benning Heights</td>\n", "      <td>2145</td>\n", "    </tr>\n", "    <tr>\n", "      <th>11</th>\n", "      <td>Cluster 23</td>\n", "      <td>Ivy City, Arboretum, Trinidad, Carver Langston</td>\n", "      <td>2110</td>\n", "    </tr>\n", "    <tr>\n", "      <th>34</th>\n", "      <td>Cluster 22</td>\n", "      <td>Brookland, Brentwood, Langdon</td>\n", "      <td>2066</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>Cluster 31</td>\n", "      <td>Deanwood, Burrville, Grant Park, Lincoln Heigh...</td>\n", "      <td>1999</td>\n", "    </tr>\n", "    <tr>\n", "      <th>31</th>\n", "      <td>Cluster 1</td>\n", "      <td>Kalorama Heights, Adams Morgan, Lanier Heights</td>\n", "      <td>1991</td>\n", "    </tr>\n", "    <tr>\n", "      <th>15</th>\n", "      <td>Cluster 9</td>\n", "      <td>Southwest Employment Area, Southwest/Waterfron...</td>\n", "      <td>1965</td>\n", "    </tr>\n", "    <tr>\n", "      <th>25</th>\n", "      <td>Cluster 10</td>\n", "      <td>Hawthorne, Barnaby Woods, Chevy Chase</td>\n", "      <td>1707</td>\n", "    </tr>\n", "    <tr>\n", "      <th>22</th>\n", "      <td>Cluster 19</td>\n", "      <td>Lamont Riggs, Queens Chapel, Fort Totten, Plea...</td>\n", "      <td>1628</td>\n", "    </tr>\n", "    <tr>\n", "      <th>17</th>\n", "      <td>Cluster 13</td>\n", "      <td>Spring Valley, Palisades, Wesley Heights, Foxh...</td>\n", "      <td>1625</td>\n", "    </tr>\n", "    <tr>\n", "      <th>18</th>\n", "      <td>Cluster 20</td>\n", "      <td>North Michigan Park, Michigan Park, University...</td>\n", "      <td>1427</td>\n", "    </tr>\n", "    <tr>\n", "      <th>37</th>\n", "      <td>Cluster 15</td>\n", "      <td>Cleveland Park, Woodley Park, Massachusetts Av...</td>\n", "      <td>1322</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Cluster 32</td>\n", "      <td>River Terrace, Benning, Greenway, Dupont Park</td>\n", "      <td>1285</td>\n", "    </tr>\n", "    <tr>\n", "      <th>36</th>\n", "      <td>Cluster 14</td>\n", "      <td>Cathedral Heights, McLean Gardens, Glover Park</td>\n", "      <td>1070</td>\n", "    </tr>\n", "    <tr>\n", "      <th>45</th>\n", "      <td>Cluster 45</td>\n", "      <td>National Mall, Potomac River</td>\n", "      <td>952</td>\n", "    </tr>\n", "    <tr>\n", "      <th>35</th>\n", "      <td>Cluster 24</td>\n", "      <td>Woodridge, Fort Lincoln, Gateway</td>\n", "      <td>941</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Cluster 30</td>\n", "      <td>Mayfair, Hillbrook, Mahaning Heights</td>\n", "      <td>778</td>\n", "    </tr>\n", "    <tr>\n", "      <th>19</th>\n", "      <td>Cluster 12</td>\n", "      <td>North Cleveland Park, Forest Hills, Van Ness</td>\n", "      <td>767</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Cluster 38</td>\n", "      <td>Douglas, Shipley Terrace</td>\n", "      <td>733</td>\n", "    </tr>\n", "    <tr>\n", "      <th>24</th>\n", "      <td>Cluster 16</td>\n", "      <td>Colonial Village, Shepherd Park, North Portal ...</td>\n", "      <td>715</td>\n", "    </tr>\n", "    <tr>\n", "      <th>26</th>\n", "      <td>Cluster 28</td>\n", "      <td>Historic Anacostia</td>\n", "      <td>678</td>\n", "    </tr>\n", "    <tr>\n", "      <th>27</th>\n", "      <td>Cluster 35</td>\n", "      <td>Fairfax Village, Naylor Gardens, Hillcrest, Su...</td>\n", "      <td>642</td>\n", "    </tr>\n", "    <tr>\n", "      <th>28</th>\n", "      <td>Cluster 37</td>\n", "      <td>Sheridan, Barry Farm, Buena Vista</td>\n", "      <td>524</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Cluster 27</td>\n", "      <td>Near Southeast, Navy Yard</td>\n", "      <td>480</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Cluster 36</td>\n", "      <td>Woodland/Fort Stanton, Garfield Heights, Knox ...</td>\n", "      <td>378</td>\n", "    </tr>\n", "    <tr>\n", "      <th>10</th>\n", "      <td>Cluster 29</td>\n", "      <td>Eastland Gardens, Kenilworth</td>\n", "      <td>191</td>\n", "    </tr>\n", "    <tr>\n", "      <th>43</th>\n", "      <td>Cluster 44</td>\n", "      <td>Joint Base Anacostia-Bolling</td>\n", "      <td>77</td>\n", "    </tr>\n", "    <tr>\n", "      <th>40</th>\n", "      <td>Cluster 41</td>\n", "      <td>Rock Creek Park</td>\n", "      <td>51</td>\n", "    </tr>\n", "    <tr>\n", "      <th>44</th>\n", "      <td>Cluster 46</td>\n", "      <td>Arboretum, Anacostia River</td>\n", "      <td>41</td>\n", "    </tr>\n", "    <tr>\n", "      <th>42</th>\n", "      <td>Cluster 43</td>\n", "      <td>Saint Elizabeths</td>\n", "      <td>26</td>\n", "    </tr>\n", "    <tr>\n", "      <th>39</th>\n", "      <td>Cluster 40</td>\n", "      <td>Walter Reed</td>\n", "      <td>12</td>\n", "    </tr>\n", "    <tr>\n", "      <th>41</th>\n", "      <td>Cluster 42</td>\n", "      <td>Observatory Circle</td>\n", "      <td>3</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["          NAME                                          NBH_NAMES  counts\n", "33   Cluster 2  Columbia Heights, Mt. Pleasant, Pleasant Plain...    5291\n", "30  Cluster 25          Union Station, Stanton Park, Kingman Park    5277\n", "20  Cluster 18               Brightwood Park, Crestwood, Petworth    5137\n", "13   Cluster 6         Dupont Circle, Connecticut Avenue/K Street    4629\n", "38  Cluster 26                         Capitol Hill, Lincoln Park    3876\n", "5    Cluster 8  Downtown, Chinatown, Penn Quarters, Mount Vern...    3763\n", "32  Cluster 21  Edgewood, Bloomingdale, Truxton Circle, Eckington    3554\n", "21  Cluster 11  Friendship Heights, American University Park, ...    2475\n", "23  Cluster 17                     Takoma, Brightwood, Manor Park    2467\n", "6    Cluster 5                        West End, Foggy Bottom, GWU    2445\n", "29  Cluster 34  Twining, Fairlawn, Randle Highlands, Penn Bran...    2429\n", "14   Cluster 3     Howard University, Le Droit Park, Cardozo/Shaw    2412\n", "0   Cluster 39   Congress Heights, Bellevue, Washington Highlands    2360\n", "9    Cluster 7                                 Shaw, Logan Circle    2270\n", "12   Cluster 4                    Georgetown, Burleith/Hillandale    2243\n", "16  Cluster 33    Capitol View, Marshall Heights, Benning Heights    2145\n", "11  Cluster 23     Ivy City, Arboretum, Trinidad, Carver Langston    2110\n", "34  Cluster 22                      Brookland, Brentwood, Langdon    2066\n", "8   Cluster 31  Deanwood, Burrville, Grant Park, Lincoln Heigh...    1999\n", "31   Cluster 1     Kalorama Heights, Adams Morgan, Lanier Heights    1991\n", "15   Cluster 9  Southwest Employment Area, Southwest/Waterfron...    1965\n", "25  Cluster 10              Hawthorne, Barnaby Woods, Chevy Chase    1707\n", "22  Cluster 19  Lamont Riggs, Queens Chapel, Fort Totten, Plea...    1628\n", "17  Cluster 13  Spring Valley, Palisades, Wesley Heights, Foxh...    1625\n", "18  Cluster 20  North Michigan Park, Michigan Park, University...    1427\n", "37  Cluster 15  Cleveland Park, Woodley Park, Massachusetts Av...    1322\n", "4   Cluster 32      River Terrace, Benning, Greenway, Dupont Park    1285\n", "36  Cluster 14     Cathedral Heights, McLean Gardens, Glover Park    1070\n", "45  Cluster 45                       National Mall, Potomac River     952\n", "35  Cluster 24                   Woodridge, Fort Lincoln, Gateway     941\n", "7   Cluster 30               Mayfair, Hillbrook, Mahaning Heights     778\n", "19  Cluster 12       North Cleveland Park, Forest Hills, Van Ness     767\n", "1   Cluster 38                           Douglas, Shipley Terrace     733\n", "24  Cluster 16  Colonial Village, Shepherd Park, North Portal ...     715\n", "26  Cluster 28                                 Historic Anacostia     678\n", "27  Cluster 35  Fairfax Village, Naylor Gardens, Hillcrest, Su...     642\n", "28  Cluster 37                  Sheridan, Barry Farm, Buena Vista     524\n", "3   Cluster 27                          Near Southeast, Navy Yard     480\n", "2   Cluster 36  Woodland/Fort Stanton, Garfield Heights, Knox ...     378\n", "10  Cluster 29                       Eastland Gardens, Kenilworth     191\n", "43  Cluster 44                       Joint Base Anacostia-Bolling      77\n", "40  Cluster 41                                    Rock Creek Park      51\n", "44  Cluster 46                         Arboretum, Anacostia River      41\n", "42  Cluster 43                                   Saint Elizabeths      26\n", "39  Cluster 40                                        Walter Reed      12\n", "41  Cluster 42                                 Observatory Circle       3"]}, "execution_count": 43, "metadata": {}, "output_type": "execute_result"}], "source": ["temp = neighborhood.sort_values(['counts'], ascending=[False])\n", "temp[['NAME', 'NBH_NAMES', 'counts']]"]}, {"cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [{"data": {"text/html": ["<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n", "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n", "                       <a href='http://dcdev.maps.arcgis.com//home/item.html?id=9610799a93fe425ea97dadf1eebea3a4' target='_blank'>\n", "                        <img src='' width='200' height='133' class=\"itemThumbnail\">\n", "                       </a>\n", "                    </div>\n", "\n", "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n", "                        <a href='http://dcdev.maps.arcgis.com//home/item.html?id=9610799a93fe425ea97dadf1eebea3a4' target='_blank'><b>Neighborhood_Service_Requests</b>\n", "                        </a>\n", "                        <br/><img src='http://dcdev.maps.arcgis.com//home/js/jsapi/esri/css/images/item_type_icons/maps16.png' style=\"vertical-align:middle;\">Web Map by mmajumdar_dcdev\n", "                        <br/>Last Modified: January 22, 2018\n", "                        <br/>0 comments, 0 views\n", "                    </div>\n", "                </div>\n", "                "], "text/plain": ["<Item title:\"Neighborhood_Service_Requests\" type:Web Map owner:mmajumdar_dcdev>"]}, "execution_count": 44, "metadata": {}, "output_type": "execute_result"}], "source": ["# Viewing the map\n", "search_result = gis.content.search(\"Neighborhood_Service_Requests\")\n", "search_result[0]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### What kind of requests does each neighborhood mostly make?"]}, {"cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [{"data": {"text/plain": ["Index(['ADDDATE', 'CITY', 'DETAILS', 'INSPECTIONDATE', 'INSPECTIONFLAG',\n", "       'INSPECTORNAME', 'Join_Count', 'LATITUDE', 'LONGITUDE',\n", "       'MARADDRESSREPOSITORYID', 'NAME', 'NBH_NAMES', 'OBJECTID',\n", "       'ORGANIZATIONACRONYM', 'PRIORITY', 'RESOLUTIONDATE', 'SERVICECALLCOUNT',\n", "       'SERVICECODE', 'SERVICECODEDESCRIPTION', 'SERVICEDUEDATE',\n", "       'SERVICEORDERDATE', 'SERVICEORDERSTATUS', 'SERVICEREQUESTID',\n", "       'SERVICETYPECODEDESCRIPTION', 'STATE', 'STATUS_CODE', 'STREETADDRESS',\n", "       'TYPE', 'WARD', 'WEB_URL', 'XCOORD', 'YCOORD', 'ZIPCODE', 'SHAPE',\n", "       'QUADRANT', 'CLUSTER', 'SERVICETYPE_NUMBER', 'STATUS_CODE_NUMBER'],\n", "      dtype='object')"]}, "execution_count": 45, "metadata": {}, "output_type": "execute_result"}], "source": ["import scipy.stats\n", "merged.columns"]}, {"cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [], "source": ["df = merged[['NAME', 'SERVICECODEDESCRIPTION']]"]}, {"cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": ["# Extract the most frequently occuring service request type, and its count\n", "df1 = df.groupby('NAME').agg(lambda x: scipy.stats.mode(x)[0][0])\n", "df2 = df.groupby('NAME').agg(lambda x: scipy.stats.mode(x)[1][0])"]}, {"cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [], "source": ["df1.reset_index(inplace=True)\n", "df2.reset_index(inplace=True)\n", "df2 = df2.rename(columns={'SERVICECODEDESCRIPTION':'SERVICECODEDESCRIPTION_COUNT'})"]}, {"cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style>\n", "    .dataframe thead tr:only-child th {\n", "        text-align: right;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>NAME</th>\n", "      <th>SERVICECODEDESCRIPTION</th>\n", "      <th>SERVICECODEDESCRIPTION_COUNT</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>Cluster 1</td>\n", "      <td>Parking Enforcement</td>\n", "      <td>383</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Cluster 10</td>\n", "      <td>Bulk Collection</td>\n", "      <td>342</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Cluster 11</td>\n", "      <td>Parking Meter Repair</td>\n", "      <td>863</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Cluster 12</td>\n", "      <td>Roadway Signs</td>\n", "      <td>111</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Cluster 13</td>\n", "      <td>Pothole</td>\n", "      <td>254</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["         NAME SERVICECODEDESCRIPTION  SERVICECODEDESCRIPTION_COUNT\n", "0   Cluster 1    Parking Enforcement                           383\n", "1  Cluster 10        Bulk Collection                           342\n", "2  Cluster 11   Parking Meter Repair                           863\n", "3  Cluster 12          Roadway Signs                           111\n", "4  Cluster 13                Pothole                           254"]}, "execution_count": 49, "metadata": {}, "output_type": "execute_result"}], "source": ["# merge the two datasets\n", "final_df = pd.merge(df1, df2, on='NAME')\n", "final_df.head()"]}, {"cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [], "source": ["# merge it with neighborhood clusters\n", "neighborhood_data = pd.merge(neighborhood, final_df, on='NAME')"]}, {"cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [{"data": {"text/html": ["<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n", "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n", "                       <a href='http://dcdev.maps.arcgis.com//home/item.html?id=575a73de3df6434fac087613e19013e3' target='_blank'>\n", "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n", "                       </a>\n", "                    </div>\n", "\n", "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n", "                        <a href='http://dcdev.maps.arcgis.com//home/item.html?id=575a73de3df6434fac087613e19013e3' target='_blank'><b>Neighborhood_Service_DC</b>\n", "                        </a>\n", "                        <br/><img src='http://dcdev.maps.arcgis.com//home/js/jsapi/esri/css/images/item_type_icons/maps16.png' style=\"vertical-align:middle;\">Web Map by mmajumdar_dcdev\n", "                        <br/>Last Modified: April 05, 2018\n", "                        <br/>0 comments, 0 views\n", "                    </div>\n", "                </div>\n", "                "], "text/plain": ["<Item title:\"Neighborhood_Service_DC\" type:Web Map owner:mmajumdar_dcdev>"]}, "execution_count": 51, "metadata": {}, "output_type": "execute_result"}], "source": ["# view the map\n", "search_result = gis.content.search(\"Neighborhood_Service_DC\")\n", "search_result[0]"]}], "metadata": {"esriNotebookRuntime": {"notebookRuntimeName": "ArcGIS Notebook Python 3 Standard", "notebookRuntimeVersion": "4.0"}, "kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}, "language_info": {"codemirror_mode": {"name": "ipython", "version": 3}, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.2"}, "toc": {"base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": true, "toc_position": {}, "toc_section_display": true, "toc_window_display": true}}, "nbformat": 4, "nbformat_minor": 2}